package cn.com.zsc.hr;

import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Vector;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import cn.com.zsc.util.ConstValue;

import com.jarp.db.DAO;

/**
 * 劳动关系变动管理类
 * @author 杨景
 *
 */
public class SocialInsuranceManage {
	/**
	 * 获取劳动关系变动列表
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String getSocialInsurance(String start, String limit) {
		String sql = "select hr.ID, hr.SINO,hr.NAME,hr.CHANGETYPE,hr.CUTOFFDATE,hr.SIGNDATE,hr.ENDDATE,"
				+ "hr.REMARKED,cm.CL_NAME from hr_echange hr inner join cm_commlist cm on hr.CHANGETYPE=cm.CL_CODE"
				+ " order by ID desc limit " + start + "," + limit;

		DAO dao = new DAO();
		
		String resultStr = ConstValue.EMPTY_RECORD_PAGEING;
		
		try {
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, sql);

			if (v != null && v.size() > 0) {
				JSONArray json = new JSONArray();
				json = JSONArray.fromObject(v);

				String siStr = json.toString();

				// 获取总共多少条
				sql = "select count(ID) from hr_echange";
				String total = dao.getDataValue(ConstValue.DBNAME, sql);

				// 对返回结果增加分页数据,返回结果增加这些后，前台需要增加相应解析
				resultStr = "{\"totalCount\":\"" + total + "\",\"results\":" + siStr + "}";
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}

		return resultStr;
	}
	
	/**
	 * 新增社保关系变动
	 * @param jso
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String insertSocialInsurance(String jso) {
		JSONObject jsobject = JSONObject.fromObject(jso);
	
		String sino = jsobject.getString("sino");
		String name = jsobject.getString("name");
		int changeType = Integer.parseInt(jsobject.getString("changeType"));
		String cutoffDate = jsobject.getString("cutoffDate");
		String signDate = jsobject.getString("signDate");
		String endDate = jsobject.getString("endDate");
		String remarked = jsobject.getString("remarked");
		
		// 判断社保号是否在员工表中存在
		String queryEmpExist = "select EID from hr_employee where SINO='" + sino + "'";
		
		// 插入变更记录
		String insertSql = "insert into hr_echange values(default,'" + sino + "','"
				+ name + "'," + changeType + ",'" + cutoffDate + "','"
				+ signDate + "','" + endDate + "','" + remarked + "')";
		
		// 更新员工表离职状态
		String updateEmpSql = "update hr_employee set LEFTJOB=40 where SINO='" + sino + "'";
		
		DAO dao = new DAO();
		
		ArrayList<String> sqlList = new ArrayList<String>();
		sqlList.add(insertSql);
		sqlList.add(updateEmpSql);
		
		try {
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, queryEmpExist);
			
			if (v != null && v.size() > 0) {
				boolean flag = dao.execCommand(ConstValue.DBNAME, sqlList);
				
				if (flag) {
					return "success";
				} else {
					return "数据库操作失败";
				}
			} else  {
				return "社保号不存在";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return "数据库操作失败";
		}
	}
	
	/**
	 * 修改社保关系变动
	 * @param jso
	 * @return
	 */
	public String updateSocialInsurance(String jso) {
		JSONObject jsobject = JSONObject.fromObject(jso);
		
		int id = Integer.parseInt(jsobject.getString("id")); 
		String sino = jsobject.getString("sino");
		String name = jsobject.getString("name");
		int changeType = Integer.parseInt(jsobject.getString("changeType"));
		String cutoffDate = jsobject.getString("cutoffDate");
		String signDate = jsobject.getString("signDate");
		String endDate = jsobject.getString("endDate");
		String remarked = jsobject.getString("remarked");
		
		String sql = "update hr_echange set SINO='" + sino + "',NAME='" + name
				+ "',CHANGETYPE=" + changeType + ",CUTOFFDATE='" + cutoffDate
				+ "',SIGNDATE='" + signDate + "',ENDDATE='" + endDate
				+ "',REMARKED='" + remarked + "' where ID=" + id;

		DAO dao = new DAO();
		
		try {
			int a = dao.execCommand(ConstValue.DBNAME, sql);
			if (a > 0) {
				return "success";
			} else {
				return "数据库操作失败";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return e.getMessage();
		}
	}
	
	/**
	 * 删除社保关系变动
	 * @param jso
	 * @return
	 */
	public String deleteSocialInsurance(String jso) {
		JSONObject jsobject = JSONObject.fromObject(jso);
		
		String id = jsobject.getString("id");
		
		String delSql = "delete from hr_echange where ID ='" + id + "'";
		
		DAO dao = new DAO();
		try {
			ArrayList<String> al = new ArrayList<String>();
			al.add(delSql);
			
			boolean flag = dao.execCommand(ConstValue.DBNAME, al);
			
			if (flag) {
				return "success";
			} else {
				return "数据库操作失败";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return e.getMessage();
		}
	}
}
